package eBrain.model;

import java.util.Hashtable;
import java.util.ArrayList;
import java.util.Enumeration;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;

import eBrain.beans.Bid;
import eBrain.beans.Category;
import eBrain.beans.Item;
import eBrain.beans.User;

public class DataManager {
	private String dbURL = "";
	private String dbUserName = "";
	private String dbPassword = "";

	public void setDbURL(String dbURL) {
		this.dbURL = dbURL;
	}

	public String getDbURL() {
		return dbURL;
	}

	public void setDbUserName(String dbUserName) {
		this.dbUserName = dbUserName;
	}

	public String getDbUserName() {
		return dbUserName;
	}

	public void setDbPassword(String dbPassword) {
		this.dbPassword = dbPassword;
	}

	public String getDbPassword() {
		return dbPassword;
	}

	public Connection getConnection() {
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(getDbURL(), getDbUserName(),
					getDbPassword());
		} catch (SQLException e) {
			System.out.println("Could not connect to DB: " + e.getMessage());
		}
		return conn;
	}

	public void putConnection(Connection conn) {
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
			}
		}
	}

	// //Item Operations
	public Item getItem(String ItemId) {
		return ItemPeer.getItem(this, ItemId);
	}

	public Item setItem(Item item) {
		return ItemPeer.setItem(this, item);
	}

	public boolean updateItem(Item item) {
		return ItemPeer.updateItem(this, item);
	}

	public ArrayList getItems() {
		return ItemPeer.getItems(this);
	}

	public ArrayList getItemsByCategory(String Cat) {
		return ItemPeer.getItemsByCategory(Cat, this);
	}

	public ArrayList getItemByUserID(int USER_ID) {
		return ItemPeer.getItemsByUserID(this, USER_ID);
	}

	// // Bids

	public ArrayList getBidsForItem(int itemid) {
		return BidPeer.getBidsForItem(this, itemid);
	}

	public boolean setBid(Bid bid) {
		return BidPeer.setBid(this, bid);
	}

	public Bid getMaxBidForItem(int itemid) {
		return BidPeer.getMaxBidForItem(this, itemid);
	}
	public Bid getNextToLast(int itemid) {
		return BidPeer.getNextToLast(this, itemid);
	}

	// //Categories

	public Hashtable getFirstLevelCategories() {
		Hashtable cats = new Hashtable();
		Connection connection = getConnection();

		if (connection != null) {

			try {
				Statement s = connection.createStatement();
				String sql = "SELECT NAME, CATEGORY_ID FROM CATEGORY WHERE SUPER_CATEGORY_ID IS NULL";

				try {
					ResultSet rs = s.executeQuery(sql);
					try {
						while (rs.next()) {
							cats.put(rs.getString(2), rs.getString(1));
						}
					} finally {
						rs.close();
					}

				} finally {
					s.close();
				}
			} catch (SQLException e) {
				System.out.println("Could not get categories: "
						+ e.getMessage());
			} finally {
				putConnection(connection);
			}
		}
		return cats;
	}

	public Hashtable getAllCategories() {
		Hashtable cats = new Hashtable();
		Connection connection = getConnection();

		if (connection != null) {

			try {
				Statement s = connection.createStatement();
				String sql = "SELECT NAME, CATEGORY_ID FROM CATEGORY";

				try {
					ResultSet rs = s.executeQuery(sql);
					try {
						while (rs.next()) {
							cats.put(rs.getString(2), rs.getString(1));
						}
					} finally {
						rs.close();
					}

				} finally {
					s.close();
				}
			} catch (SQLException e) {
				System.out.println("Could not get categories: "
						+ e.getMessage());
			} finally {
				putConnection(connection);
			}
		}
		return cats;
	}

	public ArrayList<Category> getCategoryAll() {
		Connection con = this.getConnection();
		ArrayList cl = new ArrayList<Category>();
		Category c = new Category();
		if (con != null) {
			try {
				String sql = "SELECT * FROM CATEGORY";
				Statement st = con.createStatement();
				ResultSet rs = st.executeQuery(sql);
				try {
					while (rs.next()) {
						c = new Category();
						c.setCategoryId(rs.getInt(1));
						c.setSuperCategoryId(rs.getInt(2));
						c.setCategoryLevel(rs.getInt(3));
						c.setName(rs.getString(4));
						cl.add(c);
					}
					return cl;
				} finally {
					rs.close();
					st.close();
				}

			} catch (SQLException e) {
				System.out
						.println("Kategorien konnten nicht angezeigt werden: "
								+ e.getMessage());
			} finally {
				this.putConnection(con);
			}
		}
		return cl;
	}

	public ArrayList<Category> getCategoryLevel(int catlevel, int supercat) {
		Connection con = this.getConnection();
		ArrayList cl = new ArrayList<Category>();
		Category c = new Category();
		Integer supcat = supercat;
		if (con != null) {
			try {
				String sql = "SELECT * FROM CATEGORY WHERE CATEGORY_LEVEL = "
						+ catlevel + " AND SUPER_CATEGORY_ID  ";
				if (supercat == 0) {
					sql = sql + " IS NULL";
				} else {
					sql = sql + " = " + supcat.toString();
				}
				Statement st = con.createStatement();
				ResultSet rs = st.executeQuery(sql);
				try {
					while (rs.next()) {
						c = new Category();
						c.setCategoryId(rs.getInt(1));
						c.setSuperCategoryId(rs.getInt(2));
						c.setCategoryLevel(rs.getInt(3));
						c.setName(rs.getString(4));
						cl.add(c);
					}
					return cl;
				} finally {
					rs.close();
					st.close();
				}

			} catch (SQLException e) {
				System.out
						.println("Kategorien konnten nicht angezeigt werden: "
								+ e.getMessage());
			} finally {
				this.putConnection(con);
			}
		}
		return cl;
	}

	public int getSuperCategoryId(int cid) {
		Connection con = this.getConnection();
		Category c = new Category();
		if (con != null) {
			try {
				String sql = "SELECT SUPER_CATEGORY_ID " + "FROM CATEGORY "
						+ "WHERE CATEGORY_ID = " + cid;
				Statement st = con.createStatement();
				ResultSet rs = st.executeQuery(sql);
				try {
					while (rs.next()) {
						if (c.getCategoryId() == cid)
							return c.getSuperCategoryId();
					}
				} finally {
					rs.close();
					st.close();
				}

			} catch (SQLException e) {
				System.out
						.println("Kategorien konnten nicht angezeigt werden: "
								+ e.getMessage());
			} finally {
				this.putConnection(con);
			}
		}
		return -1;
	}

	public Category getCategoryById(int id) {
		Connection con = this.getConnection();
		Category c = new Category();
		if (con != null) {
			try {
				String sql = "SELECT * FROM CATEGORY WHERE CATEGORY_ID = " + id;
				Statement st = con.createStatement();
				ResultSet rs = st.executeQuery(sql);
				try {
					c = new Category();
					c.setCategoryId(rs.getInt(1));
					c.setName(rs.getString(2));
					c.setSuperCategoryId(rs.getInt(3));
				} finally {
					rs.close();
					st.close();
				}

			} catch (SQLException e) {
				System.out
						.println("Kategorien konnten nicht angezeigt werden: "
								+ e.getMessage());
			} finally {
				this.putConnection(con);
			}
		}
		return null;
	}

	public boolean addCategory(int cid, int sid, int cl, String n) {
		Connection con = this.getConnection();
		if (con != null) {
			try {
				Statement st = con.createStatement();
				ResultSet rs = st.executeQuery("INSERT INTO CATEGORY "
						+ "VALUES (" + cid + ", " + sid + ", " + cl + ", " + n
						+ ")");
				rs.close();
				st.close();
				return true;
			} catch (SQLException e) {
				e.printStackTrace();
				return false;
			}
		}
		return false;
	}

	public boolean removeCategory(int id) {
		Connection con = this.getConnection();
		if (con != null) {
			try {
				Statement st = con.createStatement();
				ResultSet rs = st
						.executeQuery("DELETE FROM CATEGORY WHERE CATEGORY_ID = "
								+ id);
				rs.close();
				st.close();
				return true;
			} catch (SQLException e) {
				e.printStackTrace();
				return false;
			}
		}
		return false;
	}

	public String toString(Category c) {
		String s = c.getCategoryId() + "  " + c.getSuperCategoryId() + "  "
				+ c.getName();
		return s;
	}

	// / User & Registration

	public ArrayList getUsernames() {
		return UserManager.getUsernames(this);
	}

	public User getUserByUserName(String userName) {
		return UserManager.getUserByUserName(this, userName);
	}

	public String getUsernameByUserId(int userId) {
		return UserManager.getUsernameByUserId(this, userId);
	}

	public boolean validatePassword(String UserID, String Password) {
		return UserManager.validatePassword(this, UserID, Password);
	}

	public boolean updateUser(User user) {
		return UserManager.updateUser(this, user);

	}

	public boolean setUser(User user) {
		return UserManager.addUser(this, user);
	}

	public boolean hasBid(int itemid) {
		return ItemPeer.hasBid(this, itemid);
	}

}
